﻿using System;
using System.Reflection;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace BaccaratRally.DataKernel.SqlGateway
{
    using BaccaratRally.DataKernel.Entity;
    /// <summary>
    /// Sql数据库访问的代理
    /// </summary>
    public class SqlGateway
    {
        protected SqlGateway()
        {

        }

        public string ConnectionString
        {
            get;
            protected set;
        }

        /// <summary>
        /// 创建SqlGateway默认实例
        /// </summary>
        /// <returns>SqlGateway实例</returns>
        public static SqlGateway CreateInstance()
        {
            SqlGateway gateway = new SqlGateway();
            gateway.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString;
            return gateway;
        }

        /// <summary>
        /// 根据connectionString创建SqlGateway实例
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <returns>SqlGateway实例</returns>
        public static SqlGateway CreateInstance(string connectionString)
        {
            SqlGateway gateway = new SqlGateway();
            gateway.ConnectionString = connectionString;
            return gateway;
        }

        /// <summary>
        /// 插入新实体
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="entity">新实体</param>
        /// <returns>新增成功返回</returns>
        public T Insert<T>(T entity) where T : EntityBase
        {
            T result = default(T);

            string commandText = entity.GetInsertCommandText();
            SqlParameter[] parameters = entity.BuildInsertParamter();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, commandText, parameters))
            {
                if (reader.Read())
                {
                    result = EntityBase.CreateEntity<T>(reader);
                }
            }

            return result;
        }

        /// <summary>
        /// 更新实体
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="entity">实体数据</param>
        /// <returns>修改成功返回</returns>
        public T Update<T>(T entity) where T : EntityBase
        {
            T result = default(T);

            string commandText = entity.GetUpdateCommandText();
            SqlParameter[] parameters = entity.BuildUpdateParamter();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, commandText, parameters))
            {
                if (reader.Read())
                {
                    result = EntityBase.CreateEntity<T>(reader);

                }
            }

            return result;
        }

        /// <summary>
        /// 保存、更新数据实体
        /// </summary>
        /// <typeparam name="T">数据实体</typeparam>
        /// <param name="entity">数据实体</param>
        /// <param name="commandString">SQL命令/存储过程</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="sqlParams">除了实体数据外的Sql参数</param>
        /// <returns>更新成功的数据实体</returns>
        public T Save<T>(T entity, string commandString, CommandType commandType, params SqlParameter[] sqlParams) where T : EntityBase
        {
            T result = default(T);
            SqlParameter[] parameters = entity.BuildUpdateParamter();
            parameters.CopyTo(sqlParams, parameters.Length);
            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, commandType, commandString, sqlParams))
            {
                if (reader.Read())
                {
                    result = EntityBase.CreateEntity<T>(reader);
                }
            }

            return result;
        }

        /// <summary>
        /// 查询实体集合
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="commandString">TSQL查询命令/存储过程名，返回的结果集的Select投影必须符合实体对Table字段的投影</param>
        /// <param name="commandType">查询类别（命令/存储过程）</param>
        /// <param name="sqlParams">查询参数</param>
        /// <returns>实体集合</returns>
        public IList<T> Select<T>(string commandString, CommandType commandType, params SqlParameter[] sqlParams) where T : EntityBase
        {
            Collection<T> list = new Collection<T>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, commandType, commandString, sqlParams))
            {
                while (reader.Read())
                {
                    T entity = default(T);
                    entity = EntityBase.CreateEntity<T>(reader);
                    list.Add(entity);
                }
            }

            return list;
        }

        /// <summary>
        /// 查询结果集的首列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandString"></param>
        /// <param name="commandType"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public IList<T> SelectScalar<T>(string commandString, CommandType commandType, params SqlParameter[] sqlParams)
        {
            Collection<T> list = new Collection<T>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, commandType, commandString, sqlParams))
            {
                while (reader.Read())
                {
                    try
                    {
                        object dbVal = reader.GetValue(0);
                        if (dbVal == DBNull.Value || dbVal == null)
                            continue;
                        T result = (T)Convert.ChangeType(dbVal, typeof(T));
                        list.Add(result);
                    }
                    catch (System.InvalidCastException ex)
                    {
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="commandString">TSQL查询命令/存储过程名，返回的结果集的Select投影必须符合实体对Table字段的投影</param>
        /// <param name="commandType">查询类别（命令/存储过程）</param>
        /// <param name="sqlParams">查询参数</param>
        /// <returns>实体</returns>
        public T SelectSingle<T>(string commandString, CommandType commandType, params SqlParameter[] sqlParams) where T : EntityBase
        {
            T entity = default(T);
            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, commandType, commandString, sqlParams))
            {
                if (reader.Read())
                {
                    entity = EntityBase.CreateEntity<T>(reader);
                }
            }
            return entity;
        }

        /// <summary>
        /// 查询返回DataSet
        /// </summary>
        /// <param name="commandString">TSQL查询命令/存储过程名</param>
        /// <param name="commandType">查询类别（命令/存储过程）</param>
        /// <param name="sqlParams">查询参数</param>
        /// <returns>实体</returns>
        public DataSet SelectDataSet(string commandString, CommandType commandType, params SqlParameter[] sqlParams)
        {
            return SqlHelper.ExecuteDataset(ConnectionString, commandType, commandString, sqlParams);
        }

        /// <summary>
        /// 查询返回结果的首行首列的值
        /// </summary>
        /// <typeparam name="T">值的泛型类别</typeparam>
        /// <param name="commandString">TSQL查询命令/存储过程名</param>
        /// <param name="commandType">查询类别（命令/存储过程）</param>
        /// <param name="sqlParams">查询参数</param>
        /// <returns>查询结果的首行首列的值</returns>
        public T ExecuteScalar<T>(string commandString, CommandType commandType, params SqlParameter[] sqlParams)
        {
            T result = default(T);
            object dbVal = SqlHelper.ExecuteScalar(ConnectionString, commandType, commandString, sqlParams);
            if (dbVal == DBNull.Value || dbVal == null)
                return result;

            try
            {
                result = (T)Convert.ChangeType(dbVal, typeof(T));
            }
            catch (System.InvalidCastException ex)
            {
            }
            catch
            {
                throw;
            }
            return result;
        }

        /// <summary>
        /// 以字典类型返回记录集
        /// </summary>
        /// <param name="commandString"></param>
        /// <param name="commandType"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public List<FieldDictionary> SelectDictionary(string commandString, CommandType commandType, params SqlParameter[] sqlParams)
        {
            List<FieldDictionary> result = new List<FieldDictionary>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, commandType, commandString, sqlParams))
            {
                while (reader.Read())
                {
                    FieldDictionary fd = new FieldDictionary();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        fd.Add(reader.GetName(i), reader.GetValue(i));
                    }

                    result.Add(fd);
                }
            }

            return result;
        }

        /// <summary>
        /// 执行TSQL查询命令/存储过程名
        /// </summary>
        /// <param name="commandString">TSQL查询命令/存储过程名</param>
        /// <param name="commandType">查询类别（命令/存储过程）</param>
        /// <param name="sqlParams">查询参数</param>
        /// <returns>影响的结果数</returns>
        public int ExecuteNonQuery(string commandString, CommandType commandType, params SqlParameter[] sqlParams)
        {
            return SqlHelper.ExecuteNonQuery(ConnectionString, commandType, commandString, sqlParams);
        }

        public void SqlBulkCopy(string tableName, DataTable dt)
        {
            SqlHelper.SqlBulkCopy(ConnectionString, tableName, dt);
        }
    }
}
